﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spotless.Data.DataAccess.Interfaces;

namespace Spotless.Data.DataAccess.Sql
{
    public class QueryRepository : BaseRepository, IQueryRepository
    {
        public QueryRepository() : this(null) { }
        public QueryRepository(SpotlessDataContext dataContext) : base(dataContext){}

        public IQueryable<DrillDown> DrillDown_GetTopLevelByCategoryId(int categoryId, byte typeId)
        {
            return DataContext.DrillDowns.Where(x => x.CategoryId == categoryId && x.ParentId == null && x.TypeId == typeId).OrderBy(x => x.Sequence);
        }

        public IQueryable<DrillDown> DrillDown_GetAllByParentId(int id)
        {
            return DataContext.DrillDowns.Where(x => x.ParentId == id).OrderBy(x => x.Sequence);
        }

        public IQueryable<DrillDown> DrillDown_GetAllByParentId(int id, byte typeId)
        {
            return DataContext.DrillDowns.Where(x => x.ParentId == id && x.TypeId == typeId).OrderBy(x => x.Sequence);
        }

        public DrillDown DrillDown_GetById(int id)
        {
            return DataContext.DrillDowns.SingleOrDefault(x => x.DrillDownId == id);
        }

        public IQueryable<Category> Category_GetAll()
        {
            return DataContext.Categories.OrderBy(x => x.Sequence);
        }

        public int Query_CountAll()
        {
            return DataContext.Categories.Count();
        }

        public int Query_CountAllCompleted()
        {
            return Query_GetAll().Where(x => x.StatusId == (byte)Query.QueryStatus.Complete).Count();
        }

        //public int Query_CountCompletedCompensation()
        //{
            
        //    return Query_GetAll().Where(x => x.StatusId == (byte)Query.QueryStatus.Complete).Sum(x =>x.Amount ?? 0);
        //}
        
        public float Query_CountCompletedCompensation()
        {
            return Query_GetAll().Where(x => x.StatusId == (byte)Query.QueryStatus.Complete).Sum(x => x.Amount ?? 0f);
        }


        public Category Category_GetById(int id)
        {
            return DataContext.Categories.SingleOrDefault(x => x.CategoryId == id);
        }

        public Query Query_GetById(int id)
        {
            return DataContext.Queries.SingleOrDefault(x => x.QueryId == id);
        }

        public void AddQuery(Query query)
        {
            if (query.QueryId > 0)
                throw new ApplicationException(string.Format("Query '{0}' already exists in DB and cannot be added again", query.QueryId));
            DataContext.Queries.InsertOnSubmit(query);
        }


        public void Query_ApproveCompensation(int id)
        {
            //if (query.QueryId > 0)
            //    throw new ApplicationException(string.Format("Query '{0}' already exists in DB and cannot be added again", query.QueryId));
            //DataContext.Queries.InsertOnSubmit(query);
        }

        public IQueryable<Query> Query_GetAll()
        {
            return DataContext.Queries;
        }

        

        public IQueryable<Query> Query_GetOpen()
        {
            return Query_GetAll().Where(x => x.StatusId != (byte)Query.QueryStatus.Complete);
        }

        public IQueryable<Query> Query_GetCompleted()
        {
            return Query_GetAll().Where(x => x.StatusId == (byte)Query.QueryStatus.Complete);
            // = 1,
            //In_Progress = 2,
        }
        
        public IQueryable<Query> Query_GetOpenApprovedCompensation()
        {
            return Query_GetAll().Where(x => x.PaymentTypeId == (byte)Query.PaymentTypes.Compensation 
                && x.CompensationStatusId == (byte)Query.CompensationStatusTypes.Confirm
                && x.StatusId != (byte)Query.QueryStatus.Complete);
            // = 1,
            //In_Progress = 2,
        }

        public IQueryable<Query> Query_GetOpenDeclinedCompensation()
        {
            return Query_GetAll().Where(x => x.PaymentTypeId == (byte)Query.PaymentTypes.Compensation
                && x.CompensationStatusId == (byte)Query.CompensationStatusTypes.Reject
                && x.StatusId != (byte)Query.QueryStatus.Complete);
            // = 1,
            //In_Progress = 2,
        }

        //
        public IQueryable<Query> Query_GetPendingCompensation()
        {
            return Query_GetAll().Where(x => x.PaymentTypeId == (byte)Query.PaymentTypes.Compensation 
                && x.CompensationStatusId == (byte)Query.CompensationStatusTypes.Pending
                && x.StatusId != (byte)Query.QueryStatus.Complete);
            // = 1,
            //In_Progress = 2,
        }

        public IQueryable<Query> Query_GetPendingCompensationForUser(string userName)
        {
            var tmp = Query_GetPendingCompensation();
            var joined = tmp.Join(DataContext.BrandManagerProducts, x => x.ProductId, y => y.DrillDownId, (x, y) => new { Query = x, UserName = y.aspnet_User.UserName });
            return joined.Where(x => x.UserName == userName).Select(x => x.Query);
            //return joined.Where(x => x.Collection.Any(y => y.Id == 1));
            // use : return Query_GetPendingCompensation().Where(x => x.Product.BrandManagerProducts.Any(y => y.aspnet_User.UserName == userName));

            //.Where(x => x.UserName == userName).Select(x => x.Query);
            // return Query_GetPendingCompensation().Where(x => x.Collection.Any(y => y.Id == 1));
            //return Query_GetPendingCompensation().Where(x => x. BrandManagerProducts.Any(y => y.BrandManager.UserNAme == userName));
            
            
        }

        //CUSTOMERS
        public IQueryable<Customer> Customer_GetAll()
        {
            return DataContext.Customers;
        }
        
        public Customer Customer_GetById(int id)
        {
            return DataContext.Customers.SingleOrDefault(x => x.CustomerId == id);
        }
        
        public void AddCustomer(Customer customer)
        {
            if (customer.CustomerId > 0)
                throw new ApplicationException(string.Format("Cusstomer '{0}' already exists in DB and cannot be added again", customer.CustomerId));
            DataContext.Customers.InsertOnSubmit(customer);
        }

        //CAMPAIGNS
        public IQueryable<Campaign> Campaign_GetAll()
        {
            return DataContext.Campaigns;
        }

        public Campaign Campaign_GetById(int id)
        {
            return DataContext.Campaigns.SingleOrDefault(x => x.CampaignId == id);
        }

        public IQueryable<Campaign> Campaign_GetByIdList(int id)
        {
            return Campaign_GetAll().Where(x => x.CampaignId == id);
        }

        public IQueryable<CampaignUser> CampaignUser_GetAll()
        {
            return DataContext.CampaignUsers;
        }
        
        public IQueryable<CampaignUser> CampaignUser_Recipients(int id)
        {
            return CampaignUser_GetAll().Where(x => x.CampaignId == id);
            //return Customer_GetAll().Where(x => x.CategoryId== id);
        }

        //public IQueryable<CampaignUser> CampaignUser_GetAll()
        //{
        //    return DataContext.CampaignUsers;
        //}

        public void AddCampaign(Campaign campaign)
        {
            if (campaign.CampaignId > 0)
                throw new ApplicationException(string.Format("Campaign  '{0}' already exists in DB and cannot be added again", campaign.CampaignId));
            DataContext.Campaigns.InsertOnSubmit(campaign);
        }

        public IQueryable<DetailedReport> DetailedReport_Search(DateTime? from, DateTime? to, int[] categoryIDs, int[] productIDs)
        {
            var data = DataContext.DetailedReports.AsQueryable();
            if (from.HasValue)
                data = data.Where(x => x.EnquiryDate >= from);
            if (to.HasValue)
                data = data.Where(x => x.EnquiryDate <= to);
            if (categoryIDs != null && categoryIDs.Length > 0)
                data = data.Where(x => categoryIDs.Contains(x.CategoryID));
            if (productIDs != null && productIDs.Length > 0)
                data = data.Where(x => productIDs.Contains(x.ProductID));
            return data.OrderBy(x => x.CategorySequence).ThenBy(x => x.ProductSequence).ThenBy(x => x.VariationSequence ?? int.MaxValue).ThenBy(x => x.SelectionSequence ?? int.MaxValue);
        }

        public IQueryable<ComplaintsAlert> ComplaintsAlert_GetAllFormUser(string username)
        {
            return DataContext.ComplaintsAlert.Where(x => x.aspnet_User.UserName == username).OrderBy(x => x.DrillDown.Category.Sequence).ThenBy(x => x.DrillDown.Sequence);
        }

        public aspnet_User AspNetUser_GetByName(string username)
        {
            return DataContext.aspnet_Users.SingleOrDefault(x => x.UserName == username);
        }

        public void AddComplaintsAlert(ComplaintsAlert item)
        {
            DataContext.GetTable<ComplaintsAlert>().InsertOnSubmit(item);
        }

        public void DeleteComplaintsAlert(ComplaintsAlert item)
        {
            DataContext.GetTable<ComplaintsAlert>().DeleteOnSubmit(item);
        }

    }
}